--------------------------------------------------
-- ADUsers
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ADUsers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[ADUsers]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OBJECT_GUID				uniqueidentifier UNIQUE NOT NULL,
		LOGON_NAME				varchar(100) NOT NULL,
		FIRST_NAME				varchar(100) NOT NULL,
		LAST_NAME				varchar(100) NOT NULL
	);
END
GO

--------------------------------------------------
-- UserActivity
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UserActivity]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[UserActivity]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OCCURRED				datetime NOT NULL,
		USER_AD_GUID			uniqueidentifier not null,
		ACTIVITY_CODE			bigint NOT NULL
	);
END
GO

-- select * from [dbo].[UserActivity] order by OCCURRED desc

-- add column CUSTOM
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'CUSTOM' AND OBJECT_ID = object_id(N'[dbo].[UserActivity]'))
BEGIN
	ALTER TABLE [dbo].[UserActivity] ADD CUSTOM varchar(100)
END
GO

--------------------------------------------------
-- EventLogs
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[EventLogs]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OCCURRED				datetime NOT NULL,
		CONVERSATION_GUID		uniqueidentifier NOT NULL,
		CUSTOMER_URI			varchar(100),
		AGENT_AD_GUID			uniqueidentifier,
		SUPERVISOR_AD_GUID		uniqueidentifier,
		EVENT_CODE				integer NOT NULL
	);
END
GO

-- add column HW_PARTY_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'HW_PARTY_ID' AND OBJECT_ID = object_id(N'[dbo].[EventLogs]'))
BEGIN
	ALTER TABLE [dbo].[EventLogs] ADD HW_PARTY_ID bigint
END
GO

-- add column HW_APPLICANT_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'HW_APPLICANT_ID' AND OBJECT_ID = object_id(N'[dbo].[EventLogs]'))
BEGIN
	ALTER TABLE [dbo].[EventLogs] ADD HW_APPLICANT_ID integer
END
GO

-- add column CONVERSATION_CLASS_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'CONVERSATION_CLASS_ID' AND OBJECT_ID = object_id(N'[dbo].[EventLogs]'))
BEGIN
	ALTER TABLE [dbo].[EventLogs] ADD CONVERSATION_CLASS_ID integer
END
GO

-- add column ENDPOINT_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ENDPOINT_ID' AND OBJECT_ID = object_id(N'[dbo].[EventLogs]'))
BEGIN
	ALTER TABLE [dbo].[EventLogs] ADD ENDPOINT_ID integer
END
GO

--------------------------------------------------
-- Surveys
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Surveys]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Surveys]
	(
		ID					integer primary key IDENTITY NOT NULL,
		NAME				varchar(100) NOT NULL
	);
END
GO

-- add column DELETED
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'DELETED' AND OBJECT_ID = object_id(N'[dbo].[Surveys]'))
BEGIN
	ALTER TABLE [dbo].[Surveys] ADD DELETED int NULL
END
GO

-- add column TEMPLATE
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'TEMPLATE' AND OBJECT_ID = object_id(N'[dbo].[Surveys]'))
BEGIN
	ALTER TABLE [dbo].[Surveys] ADD TEMPLATE int NULL
END
GO

-- add column TYPE
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'TYPE' AND OBJECT_ID = object_id(N'[dbo].[Surveys]'))
BEGIN
	ALTER TABLE [dbo].[Surveys] ADD [TYPE] int NOT NULL DEFAULT 0
END
GO

-- add column REVIEW_TYPE
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'REVIEW_TYPE' AND OBJECT_ID = object_id(N'[dbo].[Surveys]'))
BEGIN
	ALTER TABLE [dbo].[Surveys] ADD [REVIEW_TYPE] int NOT NULL DEFAULT 0
END
GO

--------------------------------------------------
-- SurveyCategories
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SurveyCategories]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[SurveyCategories]
	(
		ID					integer primary key IDENTITY NOT NULL,
		NAME				varchar(100) NOT NULL,
		SURVEY_ID			integer references [dbo].[Surveys](ID) NOT NULL,
		PRIORITY			integer NOT NULL,

		CONSTRAINT SurveyCategories_NAME_QST_unique UNIQUE(NAME, SURVEY_ID)
	);
END
GO

-- add column DELETED
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'DELETED' AND OBJECT_ID = object_id(N'[dbo].[SurveyCategories]'))
BEGIN
	ALTER TABLE [dbo].[SurveyCategories] ADD DELETED int NULL
END
GO

-- drop UNIQUE constraint
--ALTER TABLE [dbo].[SurveyCategories] DROP CONSTRAINT SurveyCategories_NAME_QST_unique;
--GO

--------------------------------------------------
-- SurveyQuestions
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SurveyQuestions]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[SurveyQuestions]
	(
		ID						integer primary key IDENTITY NOT NULL,
		QUESTION				varchar(MAX) NOT NULL,
		SURVEY_CATEGORY_ID		integer references [dbo].SurveyCategories(ID) NOT NULL,
		PRIORITY				integer NOT NULL
	);
END
GO

-- add column DELETED
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'DELETED' AND OBJECT_ID = object_id(N'[dbo].[SurveyQuestions]'))
BEGIN
	ALTER TABLE [dbo].[SurveyQuestions] ADD DELETED int NULL
END
GO

-- add column ANSWERS_FLOW_DIRECTION
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ANSWERS_FLOW_DIRECTION' AND OBJECT_ID = object_id(N'[dbo].[SurveyQuestions]'))
BEGIN
	ALTER TABLE [dbo].[SurveyQuestions] ADD ANSWERS_FLOW_DIRECTION int NOT NULL DEFAULT 1
END
GO

--------------------------------------------------
-- SurveyAnswers
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SurveyAnswers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[SurveyAnswers]
	(
		ID						integer primary key IDENTITY NOT NULL,
		ANSWER					varchar(MAX) NOT NULL,
		SURVEY_QUESTION_ID		integer references [dbo].[SurveyQuestions](ID) NOT NULL,
		SCORE					integer NOT NULL,
		PRIORITY				integer NOT NULL
	);
END
GO

-- add column DELETED
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'DELETED' AND OBJECT_ID = object_id(N'[dbo].[SurveyAnswers]'))
BEGIN
	ALTER TABLE [dbo].[SurveyAnswers] ADD DELETED int NULL
END
GO


--------------------------------------------------
-- SurveySessions
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SurveySessions]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[SurveySessions]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OCCURRED				datetime NOT NULL default GETDATE(),
		SURVEY_ID				integer references [dbo].Surveys(ID) NOT NULL,
		CONVERSATION_GUID		uniqueidentifier NOT NULL
	);
END
GO

-- add column SURVEY_STOP
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'SURVEY_STOP' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD SURVEY_STOP varchar(MAX) NULL
END
GO

-- add column SURVEY_START
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'SURVEY_START' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD SURVEY_START varchar(MAX) NULL
END
GO

-- add column SURVEY_CONTINUE
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'SURVEY_CONTINUE' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD SURVEY_CONTINUE varchar(MAX) NULL
END
GO

-- add column SURVEY_BY
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'SURVEY_BY' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD SURVEY_BY varchar(MAX) NULL
END
GO

-- user fk ad users , is completed (bool) 

-- add column IS_COMPLETED
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'IS_COMPLETED' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD IS_COMPLETED int NULL
END
GO

-- add column DOCUMENT_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'DOCUMENT_ID' AND OBJECT_ID = object_id(N'[dbo].[SurveySessions]'))
BEGIN
	ALTER TABLE [dbo].[SurveySessions] ADD DOCUMENT_ID varchar(20) NULL

	ALTER TABLE [dbo].[SurveySessions] ALTER COLUMN CONVERSATION_GUID uniqueidentifier NULL

	ALTER TABLE [dbo].[SurveySessions] ADD CONSTRAINT [SurveySessions_check_DOCUMENT_ID_CONVERSATION_GUID] CHECK 
		((DOCUMENT_ID is not null) OR (CONVERSATION_GUID is not null))
END
GO

--------------------------------------------------
-- SurveySessionAnswers
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SurveySessionAnswers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[SurveySessionAnswers]
	(
		ID						integer primary key IDENTITY NOT NULL,
		SURVEY_SESSION_ID		integer references [dbo].SurveySessions(ID) NOT NULL,
		SURVEY_ANSWER_ID		integer references [dbo].SurveyAnswers(ID) NOT NULL,
		COMMENT					varchar(MAX)
	);
END
GO

--------------------------------------------------
-- Classifications
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Classifications]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Classifications]
	(
		ID						integer primary key IDENTITY NOT NULL,
		NAME					varchar(100) NOT NULL UNIQUE
	);
END
GO

-- add column CUSTOMER_ENDPOINT_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'CUSTOMER_ENDPOINT_ID' AND OBJECT_ID = object_id(N'[dbo].[Classifications]'))
BEGIN
	ALTER TABLE [dbo].[Classifications] ADD CUSTOMER_ENDPOINT_ID INT NULL
END
GO

-- remove unique contraint

--------------------------------------------------
---HuntGroup

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[HuntGroups]')  AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
   CREATE TABLE [dbo].[HuntGroups]
   (  
      ID                       integer primary key IDENTITY NOT NULL,
	  NAME                     varchar(100) NOT NULL UNIQUE,
	  SIP                      varchar(100) NOT NULL
	  );
END
GO

USE [QRD]
GO

/****** Object:  Index [IX_EventLogs_ConversationGUID]    Script Date: 05/25/2012 14:55:35 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EventLogs]') AND name = N'IX_EventLogs_ConversationGUID')
DROP INDEX [IX_EventLogs_ConversationGUID] ON [dbo].[EventLogs] WITH ( ONLINE = OFF )
GO

USE [QRD]
GO

/****** Object:  Index [IX_EventLogs_ConversationGUID]    Script Date: 05/25/2012 14:55:45 ******/
CREATE NONCLUSTERED INDEX [IX_EventLogs_ConversationGUID] ON [dbo].[EventLogs] 
(
	[CONVERSATION_GUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [QRD]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EventLogListener_ListenTime]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[EventLogListener] DROP CONSTRAINT [DF_EventLogListener_ListenTime]
END

--------------------------------------------------
-- ManagersAndUsers
-- add column MONTHLY_BENCHMARK
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'MONTHLY_BENCHMARK' AND OBJECT_ID = object_id(N'[dbo].[ManagersAndUsers]'))
BEGIN
	ALTER TABLE [dbo].[ManagersAndUsers] ADD MONTHLY_BENCHMARK integer
END
GO

USE [QRD]
GO

/****** Object:  Table [dbo].[EventLogListener]    Script Date: 05/25/2012 14:56:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLogListener]') AND type in (N'U'))
DROP TABLE [dbo].[EventLogListener]
GO

USE [QRD]
GO

/****** Object:  Table [dbo].[EventLogListener]    Script Date: 05/25/2012 14:56:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EventLogListener](
	[ListenID] [int] IDENTITY(1,1) NOT NULL,
	[Listener] [varchar](50) NULL,
	[ConversationGUID] [uniqueidentifier] NULL,
	[ListenTime] [datetime] NULL,
 CONSTRAINT [PK_EventLogListener] PRIMARY KEY CLUSTERED 
(
	[ListenID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EventLogListener] ADD  CONSTRAINT [DF_EventLogListener_ListenTime]  DEFAULT (getdate()) FOR [ListenTime]
GO

USE [QRD]
GO

--------------------------------------------------
-- QuestionareSessionAnswer
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[QuestionareSessionAnswer]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[QuestionareSessionAnswer]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OCCURRED				datetime NOT NULL,
		CONVERSATION_GUID		uniqueidentifier NOT NULL,
		QUESTION_ID				integer NOT NULL,
		ANSWER_CODE				integer NOT NULL
	);
END
GO